﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using DbHelper;//自定义帮助类
using Gx.Model.Entity;//实体
using Gx.Common;//通用类
namespace Gx.Dal.Employee
{
    public partial class G_PurchaseOrderGoodsInfoDal : SqlServerHelper, IDisposable
    {

        public bool AddG_PurchaseOrderGoodsInfo(G_PurchaseOrderGoodsInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("INSERT INTO G_PurchaseOrderGoodsInfo");
            strSql.Append("(");
            strSql.Append("PurchaseOrderId,");
            strSql.Append("GoodsId,");
            strSql.Append("NeedNum,");
            strSql.Append("UnitValue,");
            strSql.Append("UnitPrice,");
            strSql.Append("TotalPrice,");
            strSql.Append("Remart,");
            strSql.Append("Status,");
            strSql.Append("AddUser,");
            strSql.Append("AddTime,");
            strSql.Append("LastEditUser,");
            strSql.Append("LastEditTime");
            strSql.Append(")");
            strSql.Append("VALUES");
            strSql.Append("(");
            strSql.Append("@PurchaseOrderId,");
            strSql.Append("@GoodsId,");
            strSql.Append("@NeedNum,");
            strSql.Append("@UnitValue,");
            strSql.Append("@UnitPrice,");
            strSql.Append("@TotalPrice,");
            strSql.Append("@Remart,");
            strSql.Append("@Status,");
            strSql.Append("@AddUser,");
            strSql.Append("@AddTime,");
            strSql.Append("@LastEditUser,");
            strSql.Append("@LastEditTime");
            strSql.Append(")");
            List<SqlParameter> pList = new List<SqlParameter>();
            pList.Add(new SqlParameter("@PurchaseOrderId", SqlDbType.Int, 4) { Value = model.PurchaseOrderId });
            pList.Add(new SqlParameter("@GoodsId", SqlDbType.Int, 4) { Value = model.GoodsId });
            pList.Add(new SqlParameter("@NeedNum", SqlDbType.Decimal, 9) { Value = model.NeedNum });
            pList.Add(new SqlParameter("@UnitValue", SqlDbType.Int, 4) { Value = model.UnitValue });
            pList.Add(new SqlParameter("@UnitPrice", SqlDbType.Decimal, 9) { Value = model.UnitPrice });
            pList.Add(new SqlParameter("@TotalPrice", SqlDbType.Decimal, 9) { Value = model.TotalPrice });
            pList.Add(new SqlParameter("@Remart", SqlDbType.NVarChar, 200) { Value = model.Remart });
            pList.Add(new SqlParameter("@Status", SqlDbType.Int, 4) { Value = model.Status });
            pList.Add(new SqlParameter("@AddUser", SqlDbType.Int, 4) { Value = model.AddUser });
            pList.Add(new SqlParameter("@AddTime", SqlDbType.DateTime) { Value = model.AddTime });
            pList.Add(new SqlParameter("@LastEditUser", SqlDbType.Int, 4) { Value = model.LastEditUser });
            pList.Add(new SqlParameter("@LastEditTime", SqlDbType.DateTime) { Value = model.LastEditTime });
            return ExecuteNonQuery(ConfigHelper.ConnectionStrings, strSql.ToString(), pList.ToArray()) > 0;
        }

        public bool DelG_PurchaseOrderGoodsInfo(int PurchaseOrderGoodsId)
        {
            string strSql = "DELETE FROM G_PurchaseOrderGoodsInfo WHERE PurchaseOrderGoodsId=@PurchaseOrderGoodsId";
            List<SqlParameter> pList = new List<SqlParameter>();
            pList.Add(new SqlParameter("@PurchaseOrderGoodsId", SqlDbType.Int, 4) { Value = PurchaseOrderGoodsId });
            return ExecuteNonQuery(ConfigHelper.ConnectionStrings, strSql.ToString(), pList.ToArray()) > 0;
        }

        public bool DelG_PurchaseOrderGoodsInfo(G_PurchaseOrderGoodsInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE G_PurchaseOrderGoodsInfo SET ");
            strSql.Append("[Status]=@Status  ");
            strSql.Append("LastEditUser=@LastEditUser, ");
            strSql.Append("LastEditTime=@LastEditTime ");
            strSql.Append("WHERE PurchaseOrderGoodsId=@PurchaseOrderGoodsId  ");
            List<SqlParameter> pList = new List<SqlParameter>();
            pList.Add(new SqlParameter("@PurchaseOrderGoodsId", SqlDbType.Int, 4) { Value = model.PurchaseOrderGoodsId });
            pList.Add(new SqlParameter("@Status", SqlDbType.TinyInt) { Value = model.Status });
            pList.Add(new SqlParameter("@LastEditUser", SqlDbType.Int, 4) { Value = model.LastEditUser });
            pList.Add(new SqlParameter("@LastEditTime", SqlDbType.DateTime) { Value = model.LastEditTime });
            return ExecuteNonQuery(ConfigHelper.ConnectionStrings, strSql.ToString(), pList.ToArray()) > 0;
        }

        public bool EditG_PurchaseOrderGoodsInfo(G_PurchaseOrderGoodsInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE G_PurchaseOrderGoodsInfo SET ");
            strSql.Append(" PurchaseOrderId=@PurchaseOrderId,");
            strSql.Append(" GoodsId=@GoodsId,");
            strSql.Append(" NeedNum=@NeedNum,");
            strSql.Append(" UnitValue=@UnitValue,");
            strSql.Append(" UnitPrice=@UnitPrice,");
            strSql.Append(" TotalPrice=@TotalPrice,");
            strSql.Append(" Remart=@Remart,");
            strSql.Append(" Status=@Status,");
            strSql.Append(" AddUser=@AddUser,");
            strSql.Append(" AddTime=@AddTime,");
            strSql.Append(" LastEditUser=@LastEditUser,");
            strSql.Append(" LastEditTime=@LastEditTime");
            strSql.Append(" WHERE PurchaseOrderGoodsId=@PurchaseOrderGoodsId");
            List<SqlParameter> pList = new List<SqlParameter>();
            pList.Add(new SqlParameter("@PurchaseOrderGoodsId", SqlDbType.Int, 4) { Value = model.PurchaseOrderGoodsId });
            pList.Add(new SqlParameter("@PurchaseOrderId", SqlDbType.Int, 4) { Value = model.PurchaseOrderId });
            pList.Add(new SqlParameter("@GoodsId", SqlDbType.Int, 4) { Value = model.GoodsId });
            pList.Add(new SqlParameter("@NeedNum", SqlDbType.Decimal, 9) { Value = model.NeedNum });
            pList.Add(new SqlParameter("@UnitValue", SqlDbType.Int, 4) { Value = model.UnitValue });
            pList.Add(new SqlParameter("@UnitPrice", SqlDbType.Decimal, 9) { Value = model.UnitPrice });
            pList.Add(new SqlParameter("@TotalPrice", SqlDbType.Decimal, 9) { Value = model.TotalPrice });
            pList.Add(new SqlParameter("@Remart", SqlDbType.NVarChar, 200) { Value = model.Remart });
            pList.Add(new SqlParameter("@Status", SqlDbType.Int, 4) { Value = model.Status });
            pList.Add(new SqlParameter("@AddUser", SqlDbType.Int, 4) { Value = model.AddUser });
            pList.Add(new SqlParameter("@AddTime", SqlDbType.DateTime) { Value = model.AddTime });
            pList.Add(new SqlParameter("@LastEditUser", SqlDbType.Int, 4) { Value = model.LastEditUser });
            pList.Add(new SqlParameter("@LastEditTime", SqlDbType.DateTime) { Value = model.LastEditTime });
            return ExecuteNonQuery(ConfigHelper.ConnectionStrings, strSql.ToString(), pList.ToArray()) > 0;
        }

        public List<G_PurchaseOrderGoodsInfo> GetG_PurchaseOrderGoodsInfoList(string strWhere)
        {
            List<G_PurchaseOrderGoodsInfo> list = new List<G_PurchaseOrderGoodsInfo>();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT ");
            strSql.Append(" PurchaseOrderGoodsId");
            strSql.Append(",PurchaseOrderId");
            strSql.Append(",GoodsId");
            strSql.Append(",NeedNum");
            strSql.Append(",UnitValue");
            strSql.Append(",UnitPrice");
            strSql.Append(",TotalPrice");
            strSql.Append(",Remart");
            strSql.Append(",Status");
            strSql.Append(",AddUser");
            strSql.Append(",AddTime");
            strSql.Append(",LastEditUser");
            strSql.Append(",LastEditTime");
            strSql.AppendFormat(" FROM G_PurchaseOrderGoodsInfo WITH(NOLOCK) WHERE [Status]=0 {0}", strWhere);
            using (SqlDataReader r = ExecuteReader(ConfigHelper.ConnectionStrings, strSql.ToString()))
            {
                int PurchaseOrderGoodsId = r.GetOrdinal("PurchaseOrderGoodsId");
                int PurchaseOrderId = r.GetOrdinal("PurchaseOrderId");
                int GoodsId = r.GetOrdinal("GoodsId");
                int NeedNum = r.GetOrdinal("NeedNum");
                int UnitValue = r.GetOrdinal("UnitValue");
                int UnitPrice = r.GetOrdinal("UnitPrice");
                int TotalPrice = r.GetOrdinal("TotalPrice");
                int Remart = r.GetOrdinal("Remart");
                int Status = r.GetOrdinal("Status");
                int AddUser = r.GetOrdinal("AddUser");
                int AddTime = r.GetOrdinal("AddTime");
                int LastEditUser = r.GetOrdinal("LastEditUser");
                int LastEditTime = r.GetOrdinal("LastEditTime");
                G_PurchaseOrderGoodsInfo model = null;
                while (r.Read())
                {
                    model = new G_PurchaseOrderGoodsInfo();
                    model.PurchaseOrderGoodsId = r.GetInt32(PurchaseOrderGoodsId);
                    model.PurchaseOrderId = r.GetInt32(PurchaseOrderId);
                    model.GoodsId = r.GetInt32(GoodsId);
                    model.NeedNum = r.GetDecimal(NeedNum);
                    model.UnitValue = r.GetInt32(UnitValue);
                    model.UnitPrice = r.GetDecimal(UnitPrice);
                    model.TotalPrice = r.GetDecimal(TotalPrice);
                    model.Remart = r.GetString(Remart);
                    model.Status = r.GetInt32(Status);
                    model.AddUser = r.GetInt32(AddUser);
                    model.AddTime = r.GetDateTime(AddTime);
                    model.LastEditUser = r.GetInt32(LastEditUser);
                    model.LastEditTime = r.GetDateTime(LastEditTime);
                    list.Add(model);
                }
                r.Close();
            }
            return list;
        }

        public List<G_PurchaseOrderGoodsInfo> GetG_PurchaseOrderGoodsInfoList(string strWhere, int pageIndex, int pageSize, out int totalCount)
        {
            List<G_PurchaseOrderGoodsInfo> list = new List<G_PurchaseOrderGoodsInfo>();
            totalCount = GetG_PurchaseOrderGoodsInfoCount(strWhere);
            if (totalCount == 0)
            {
                return list;
            }
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM (");
            strSql.Append("SELECT ROW_NUMBER() OVER(ORDER BY PurchaseOrderGoodsId DESC) AS ORDERBYID");
            strSql.Append(",PurchaseOrderGoodsId");
            strSql.Append(",PurchaseOrderId");
            strSql.Append(",GoodsId");
            strSql.Append(",NeedNum");
            strSql.Append(",UnitValue");
            strSql.Append(",UnitPrice");
            strSql.Append(",TotalPrice");
            strSql.Append(",Remart");
            strSql.Append(",Status");
            strSql.Append(",AddUser");
            strSql.Append(",AddTime");
            strSql.Append(",LastEditUser");
            strSql.Append(",LastEditTime");
            strSql.AppendFormat(" FROM G_PurchaseOrderGoodsInfo WITH(NOLOCK) WHERE [Status]=0 {0}) AS T WHERE T.ORDERBYID  BETWEEN {1} AND {2}", strWhere, pageIndex, pageSize);
            using (SqlDataReader r = ExecuteReader(ConfigHelper.ConnectionStrings, strSql.ToString()))
            {
                int PurchaseOrderGoodsId = r.GetOrdinal("PurchaseOrderGoodsId");
                int PurchaseOrderId = r.GetOrdinal("PurchaseOrderId");
                int GoodsId = r.GetOrdinal("GoodsId");
                int NeedNum = r.GetOrdinal("NeedNum");
                int UnitValue = r.GetOrdinal("UnitValue");
                int UnitPrice = r.GetOrdinal("UnitPrice");
                int TotalPrice = r.GetOrdinal("TotalPrice");
                int Remart = r.GetOrdinal("Remart");
                int Status = r.GetOrdinal("Status");
                int AddUser = r.GetOrdinal("AddUser");
                int AddTime = r.GetOrdinal("AddTime");
                int LastEditUser = r.GetOrdinal("LastEditUser");
                int LastEditTime = r.GetOrdinal("LastEditTime");
                G_PurchaseOrderGoodsInfo model = null;
                while (r.Read())
                {
                    model = new G_PurchaseOrderGoodsInfo();
                    model.PurchaseOrderGoodsId = r.GetInt32(PurchaseOrderGoodsId);
                    model.PurchaseOrderId = r.GetInt32(PurchaseOrderId);
                    model.GoodsId = r.GetInt32(GoodsId);
                    model.NeedNum = r.GetDecimal(NeedNum);
                    model.UnitValue = r.GetInt32(UnitValue);
                    model.UnitPrice = r.GetDecimal(UnitPrice);
                    model.TotalPrice = r.GetDecimal(TotalPrice);
                    model.Remart = r.GetString(Remart);
                    model.Status = r.GetInt32(Status);
                    model.AddUser = r.GetInt32(AddUser);
                    model.AddTime = r.GetDateTime(AddTime);
                    model.LastEditUser = r.GetInt32(LastEditUser);
                    model.LastEditTime = r.GetDateTime(LastEditTime);
                    list.Add(model);
                }
                r.Close();
            }
            return list;
        }

        public int GetG_PurchaseOrderGoodsInfoCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendFormat("SELECT COUNT(*) FROM G_PurchaseOrderGoodsInfo WITH(NOLOCK) WHERE [Status]=0 {0}", strWhere);
            return int.Parse(ExecuteScalar(ConfigHelper.ConnectionStrings, strSql.ToString()).ToString());
        }

        public G_PurchaseOrderGoodsInfo GetG_PurchaseOrderGoodsInfoModel(int purchaseOrderGoodsId)
        {
            G_PurchaseOrderGoodsInfo model = new G_PurchaseOrderGoodsInfo();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT ");
            strSql.Append("PurchaseOrderGoodsId");
            strSql.Append(",PurchaseOrderId");
            strSql.Append(",GoodsId");
            strSql.Append(",NeedNum");
            strSql.Append(",UnitValue");
            strSql.Append(",UnitPrice");
            strSql.Append(",TotalPrice");
            strSql.Append(",Remart");
            strSql.Append(",Status");
            strSql.Append(",AddUser");
            strSql.Append(",AddTime");
            strSql.Append(",LastEditUser");
            strSql.Append(",LastEditTime");
            strSql.Append(" FROM G_PurchaseOrderGoodsInfo WITH(NOLOCK) WHERE PurchaseOrderGoodsId=@PurchaseOrderGoodsId");
            List<SqlParameter> pList = new List<SqlParameter>();
            pList.Add(new SqlParameter("@PurchaseOrderGoodsId", SqlDbType.Int, 4) { Value = purchaseOrderGoodsId });
            using (SqlDataReader r = this.ExecuteReader(ConfigHelper.ConnectionStrings, strSql.ToString(), pList.ToArray()))
            {
                int PurchaseOrderGoodsId = r.GetOrdinal("PurchaseOrderGoodsId");
                int PurchaseOrderId = r.GetOrdinal("PurchaseOrderId");
                int GoodsId = r.GetOrdinal("GoodsId");
                int NeedNum = r.GetOrdinal("NeedNum");
                int UnitValue = r.GetOrdinal("UnitValue");
                int UnitPrice = r.GetOrdinal("UnitPrice");
                int TotalPrice = r.GetOrdinal("TotalPrice");
                int Remart = r.GetOrdinal("Remart");
                int Status = r.GetOrdinal("Status");
                int AddUser = r.GetOrdinal("AddUser");
                int AddTime = r.GetOrdinal("AddTime");
                int LastEditUser = r.GetOrdinal("LastEditUser");
                int LastEditTime = r.GetOrdinal("LastEditTime");
                if (r.Read())
                {
                    model.PurchaseOrderGoodsId = r.GetInt32(PurchaseOrderGoodsId);
                    model.PurchaseOrderId = r.GetInt32(PurchaseOrderId);
                    model.GoodsId = r.GetInt32(GoodsId);
                    model.NeedNum = r.GetDecimal(NeedNum);
                    model.UnitValue = r.GetInt32(UnitValue);
                    model.UnitPrice = r.GetDecimal(UnitPrice);
                    model.TotalPrice = r.GetDecimal(TotalPrice);
                    model.Remart = r.GetString(Remart);
                    model.Status = r.GetInt32(Status);
                    model.AddUser = r.GetInt32(AddUser);
                    model.AddTime = r.GetDateTime(AddTime);
                    model.LastEditUser = r.GetInt32(LastEditUser);
                    model.LastEditTime = r.GetDateTime(LastEditTime);
                }
                r.Close();
            }
            return model;
        }
    }
}
